from flask import Blueprint, jsonify, request
import pg8000

postgresql_bp = Blueprint('postgresql', __name__)

# 启动postgresql，net start postgresql-x64-17.2
# 停止postgresql，net stop postgresql-x64-17.2
# 配置环境 D:\app\postgresql17、D:\app\postgresql17\bin、D:\app\postgresql17\lib
DB_CONFIG = {
    #'dbname': 'area', # psycopg2的使用方式
    'database': 'area',
    'user': 'postgres',
    'password': 'postgres',
    'host': 'localhost',
    # 'port': '5432' # psycopg2的使用方式
    'port': 5432
}

# psycopg2的使用方式
# def get_db_connection():
#     try:
#         conn = psycopg2.connect(**DB_CONFIG)
#         return conn
#     except Exception as e:
#         print(f"数据库连接错误: {e}")
#         return None

def get_db_connection():
    try:
        # pgdb 的连接方式略有不同
        conn = pg8000.connect(**DB_CONFIG)
        return conn
    except Exception as e:
        print(f"数据库连接错误: {e}")
        return None 

@postgresql_bp.route('/users', methods=['GET'])
def get_users():
    conn = get_db_connection()
    if conn:
        try:
            # cur = conn.cursor(cursor_factory=RealDictCursor) # psycopg2的使用方式
            cur = conn.cursor()

            cur.execute('SELECT * FROM users')
            
            # users = cur.fetchall()  # psycopg2的使用方式
            columns = [desc[0] for desc in cur.description]
            users = [dict(zip(columns, row)) for row in cur.fetchall()]

            cur.close()
            conn.close()
            return jsonify(users)
        except Exception as e:
            return jsonify({'error': str(e)}), 500
    return jsonify({'error': '数据库连接失败'}), 500

@postgresql_bp.route('/users', methods=['POST'])
def create_user():
    data = request.get_json()
    conn = get_db_connection()
    if conn:
        try:
            cur = conn.cursor()
            cur.execute(
                'INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id',
                (data['name'], data['email'])
            )
            user_id = cur.fetchone()[0]
            conn.commit()
            cur.close()
            conn.close()
            return jsonify({'id': user_id, 'message': '用户创建成功'}), 201
        except Exception as e:
            return jsonify({'error': str(e)}), 500
    return jsonify({'error': '数据库连接失败'}), 500

@postgresql_bp.route('/users/<int:user_id>', methods=['PUT'])
def update_user(user_id):
    data = request.get_json()
    conn = get_db_connection()
    if conn:
        try:
            cur = conn.cursor()
            cur.execute(
                'UPDATE users SET name = %s, email = %s WHERE id = %s',
                (data['name'], data['email'], user_id)
            )
            conn.commit()
            cur.close()
            conn.close()
            return jsonify({'message': '用户更新成功'})
        except Exception as e:
            return jsonify({'error': str(e)}), 500
    return jsonify({'error': '数据库连接失败'}), 500

@postgresql_bp.route('/users/<int:user_id>', methods=['DELETE'])
def delete_user(user_id):
    conn = get_db_connection()
    if conn:
        try:
            cur = conn.cursor()
            cur.execute('DELETE FROM users WHERE id = %s', (user_id,))
            conn.commit()
            cur.close()
            conn.close()
            return jsonify({'message': '用户删除成功'})
        except Exception as e:
            return jsonify({'error': str(e)}), 500
    return jsonify({'error': '数据库连接失败'}), 500

@postgresql_bp.route('/getUserInfo', methods=['GET'])
def getUserInfo():
    return "user info"
